<!DOCTYPE html>
<html lang=zh>
<head>
  <meta charset="utf-8">
  
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui">
  <meta name="renderer" content="webkit">
  <meta http-equiv="Cache-Control" content="no-transform" />
  <meta http-equiv="Cache-Control" content="no-siteapp" />
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="apple-mobile-web-app-status-bar-style" content="black">
  <meta name="format-detection" content="telephone=no,email=no,adress=no">
  <!-- Color theme for statusbar -->
  <meta name="theme-color" content="#000000" />
  <!-- 强制页面在当前窗口以独立页面显示,防止别人在框架里调用页面 -->
  <meta http-equiv="window-target" content="_top" />
  
  
  <title>常用的sql语句 | Dreamcat</title>
  <meta name="description" content="创建数据库并制定编码 Create database 数据库名 default character set utf8; Create database ssm default character set utf8;   删除数据库 drop database 数据名; drop database ssm; 创建表 一般情况：  Create table 表名 (     列名 类型 约束 auto">
<meta property="og:type" content="article">
<meta property="og:title" content="常用的sql语句">
<meta property="og:url" content="http://dreamcat.ink/2019/07/14/chang-yong-de-sql-yu-ju/index.html">
<meta property="og:site_name" content="Dreamcat">
<meta property="og:description" content="创建数据库并制定编码 Create database 数据库名 default character set utf8; Create database ssm default character set utf8;   删除数据库 drop database 数据名; drop database ssm; 创建表 一般情况：  Create table 表名 (     列名 类型 约束 auto">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://raw.githubusercontent.com/DreamCats/PicBed/master/20191121170733.png">
<meta property="og:image" content="https://raw.githubusercontent.com/DreamCats/PicBed/master/20191121170817.png">
<meta property="og:image" content="https://raw.githubusercontent.com/DreamCats/PicBed/master/20191121170856.png">
<meta property="article:published_time" content="2019-07-14T09:31:56.000Z">
<meta property="article:modified_time" content="2020-10-29T15:55:13.068Z">
<meta property="article:author" content="买斯基">
<meta property="article:tag" content="db">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://raw.githubusercontent.com/DreamCats/PicBed/master/20191121170733.png">
  <!-- Canonical links -->
  <link rel="canonical" href="http://dreamcat.ink/2019/07/14/chang-yong-de-sql-yu-ju/index.html">
  
    <link rel="alternate" href="/atom.xml" title="Dreamcat" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png" type="image/x-icon">
  
  
<link rel="stylesheet" href="/css/style.css">

  
  
  
  
<meta name="generator" content="Hexo 4.2.0"><link rel="stylesheet" href="/css/prism-tomorrow.css" type="text/css"></head>


<body class="main-center" itemscope itemtype="http://schema.org/WebPage">
  <header class="header" itemscope itemtype="http://schema.org/WPHeader">
  <div class="slimContent">
    <div class="navbar-header">
      
      
      <div class="profile-block text-center">
        <a id="avatar" href="https://github.com/DreamCats" target="_blank">
          <img class="img-circle img-rotate" src="/images/avatar.jpg" width="200" height="200">
        </a>
        <h2 id="name" class="hidden-xs hidden-sm">Dreamcat</h2>
        <h3 id="title" class="hidden-xs hidden-sm hidden-md">ドリームキャット</h3>
        <small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i> Chengdu, China</small>
      </div>
      
      <div class="search" id="search-form-wrap">

    <form class="search-form sidebar-form">
        <div class="input-group">
            <input type="text" class="search-form-input form-control" placeholder="搜索" />
            <span class="input-group-btn">
                <button type="submit" class="search-form-submit btn btn-flat" onclick="return false;"><i class="icon icon-search"></i></button>
            </span>
        </div>
    </form>
    <div class="ins-search">
  <div class="ins-search-mask"></div>
  <div class="ins-search-container">
    <div class="ins-input-wrapper">
      <input type="text" class="ins-search-input" placeholder="想要查找什么..." x-webkit-speech />
      <button type="button" class="close ins-close ins-selectable" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
    </div>
    <div class="ins-section-wrapper">
      <div class="ins-section-container"></div>
    </div>
  </div>
</div>


</div>
      <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
    </div>
    <nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation">
      <ul class="nav navbar-nav main-nav ">
        
        
        <li class="menu-item menu-item-home">
          <a href="/.">
            
            <i class="icon icon-home-fill"></i>
            
            <span class="menu-title">首页</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-archives">
          <a href="/archives">
            
            <i class="icon icon-archives-fill"></i>
            
            <span class="menu-title">归档</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-categories">
          <a href="/categories">
            
            <i class="icon icon-folder"></i>
            
            <span class="menu-title">分类</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-tags">
          <a href="/tags">
            
            <i class="icon icon-tags"></i>
            
            <span class="menu-title">标签</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-links">
          <a href="/links">
            
            <i class="icon icon-friendship"></i>
            
            <span class="menu-title">友链</span>
          </a>
        </li>
        
        
        <li class="menu-item menu-item-about">
          <a href="/about">
            
            <i class="icon icon-cup-fill"></i>
            
            <span class="menu-title">关于</span>
          </a>
        </li>
        
      </ul>
      
	
    <ul class="social-links">
    	
        <li><a href="https://github.com/DreamCats" target="_blank" title="Github" data-toggle=tooltip data-placement=top><i class="icon icon-github"></i></a></li>
        
        <li><a href="https://github.com/DreamCats" target="_blank" title="Weibo" data-toggle=tooltip data-placement=top><i class="icon icon-weibo"></i></a></li>
        
        <li><a href="https://github.com/DreamCats" target="_blank" title="Twitter" data-toggle=tooltip data-placement=top><i class="icon icon-twitter"></i></a></li>
        
        <li><a href="https://github.com/DreamCats" target="_blank" title="Behance" data-toggle=tooltip data-placement=top><i class="icon icon-behance"></i></a></li>
        
        <li><a href="/atom.xml" target="_blank" title="Rss" data-toggle=tooltip data-placement=top><i class="icon icon-rss"></i></a></li>
        
    </ul>

    </nav>
  </div>
</header>

  
    <aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    
      <div class="widget">
    <h3 class="widget-title">公告</h3>
    <div class="widget-body">
        <div id="board">
            <div class="content">
                <p>dream!</p>
            </div>
        </div>
    </div>
</div>

    
      
  <div class="widget">
    <h3 class="widget-title">分类</h3>
    <div class="widget-body">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/algorithm/">algorithm</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/db/">db</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/java/">java</a><span class="category-list-count">17</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/linux/">linux</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/spring/">spring</a><span class="category-list-count">9</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/vue/">vue</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/web/">web</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E5%B7%A5%E5%85%B7/">工具</a><span class="category-list-count">17</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E7%A7%8B%E6%8B%9B/">秋招</a><span class="category-list-count">11</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">标签</h3>
    <div class="widget-body">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/Dubbo/" rel="tag">Dubbo</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/MQ/" rel="tag">MQ</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/db/" rel="tag">db</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/dubbo/" rel="tag">dubbo</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/frp/" rel="tag">frp</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/github/" rel="tag">github</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java/" rel="tag">java</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java%E5%9F%BA%E7%A1%80/" rel="tag">java基础</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java%E9%9B%86%E5%90%88/" rel="tag">java集合</a><span class="tag-list-count">11</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/jvm/" rel="tag">jvm</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/linux/" rel="tag">linux</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mongodb/" rel="tag">mongodb</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mybatis/" rel="tag">mybatis</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/nvm/" rel="tag">nvm</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/python/" rel="tag">python</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/redis/" rel="tag">redis</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/spring/" rel="tag">spring</a><span class="tag-list-count">9</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/ubuntu/" rel="tag">ubuntu</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/vscode/" rel="tag">vscode</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/vue/" rel="tag">vue</a><span class="tag-list-count">3</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/web/" rel="tag">web</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E5%A4%9A%E7%BA%BF%E7%A8%8B/" rel="tag">多线程</a><span class="tag-list-count">7</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E7%AE%97%E6%B3%95/" rel="tag">算法</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E7%BB%88%E7%AB%AF/" rel="tag">终端</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E8%99%9A%E6%8B%9F%E6%9C%BA/" rel="tag">虚拟机</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C/" rel="tag">计算机网络</a><span class="tag-list-count">1</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">标签云</h3>
    <div class="widget-body tagcloud">
      <a href="/tags/Dubbo/" style="font-size: 13px;">Dubbo</a> <a href="/tags/MQ/" style="font-size: 13.2px;">MQ</a> <a href="/tags/db/" style="font-size: 13.4px;">db</a> <a href="/tags/dubbo/" style="font-size: 13px;">dubbo</a> <a href="/tags/frp/" style="font-size: 13px;">frp</a> <a href="/tags/github/" style="font-size: 13.4px;">github</a> <a href="/tags/java/" style="font-size: 13px;">java</a> <a href="/tags/java%E5%9F%BA%E7%A1%80/" style="font-size: 13.2px;">java基础</a> <a href="/tags/java%E9%9B%86%E5%90%88/" style="font-size: 14px;">java集合</a> <a href="/tags/jvm/" style="font-size: 13px;">jvm</a> <a href="/tags/linux/" style="font-size: 13px;">linux</a> <a href="/tags/mongodb/" style="font-size: 13px;">mongodb</a> <a href="/tags/mybatis/" style="font-size: 13.2px;">mybatis</a> <a href="/tags/nvm/" style="font-size: 13px;">nvm</a> <a href="/tags/python/" style="font-size: 13px;">python</a> <a href="/tags/redis/" style="font-size: 13.2px;">redis</a> <a href="/tags/spring/" style="font-size: 13.8px;">spring</a> <a href="/tags/ubuntu/" style="font-size: 13px;">ubuntu</a> <a href="/tags/vscode/" style="font-size: 13px;">vscode</a> <a href="/tags/vue/" style="font-size: 13.4px;">vue</a> <a href="/tags/web/" style="font-size: 13px;">web</a> <a href="/tags/%E5%A4%9A%E7%BA%BF%E7%A8%8B/" style="font-size: 13.6px;">多线程</a> <a href="/tags/%E7%AE%97%E6%B3%95/" style="font-size: 13px;">算法</a> <a href="/tags/%E7%BB%88%E7%AB%AF/" style="font-size: 13.2px;">终端</a> <a href="/tags/%E8%99%9A%E6%8B%9F%E6%9C%BA/" style="font-size: 13px;">虚拟机</a> <a href="/tags/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C/" style="font-size: 13px;">计算机网络</a>
    </div>
  </div>

    
      
  <div class="widget">
    <h3 class="widget-title">归档</h3>
    <div class="widget-body">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/04/">四月 2020</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/03/">三月 2020</a><span class="archive-list-count">8</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/02/">二月 2020</a><span class="archive-list-count">6</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/01/">一月 2020</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/12/">十二月 2019</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/11/">十一月 2019</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">十月 2019</a><span class="archive-list-count">8</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/09/">九月 2019</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">八月 2019</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/07/">七月 2019</a><span class="archive-list-count">7</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">六月 2019</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/05/">五月 2019</a><span class="archive-list-count">9</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/03/">三月 2019</a><span class="archive-list-count">1</span></li></ul>
    </div>
  </div>


    
      
  <div class="widget">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget-body">
      <ul class="recent-post-list list-unstyled no-thumbnail">
        
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/%E7%A7%8B%E6%8B%9B/">秋招</a>
              </p>
              <p class="item-title">
                <a href="/2020/04/02/ge-ren-tu-xie-xi-lie-zong-jie-ji-suan-ji-wang-luo/" class="title">个人吐血系列-总结计算机网络</a>
              </p>
              <p class="item-date">
                <time datetime="2020-04-01T17:42:22.000Z" itemprop="datePublished">2020-04-02</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/%E7%A7%8B%E6%8B%9B/">秋招</a>
              </p>
              <p class="item-title">
                <a href="/2020/04/01/ge-ren-tu-xie-xi-lie-zong-jie-rocketmq/" class="title">个人吐血系列-总结RocketMQ</a>
              </p>
              <p class="item-date">
                <time datetime="2020-04-01T04:42:56.000Z" itemprop="datePublished">2020-04-01</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/%E7%A7%8B%E6%8B%9B/">秋招</a>
              </p>
              <p class="item-title">
                <a href="/2020/04/01/ge-ren-tu-xie-xi-lie-zong-jie-dubbo/" class="title">个人吐血系列-总结Dubbo</a>
              </p>
              <p class="item-date">
                <time datetime="2020-03-31T16:04:46.000Z" itemprop="datePublished">2020-04-01</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/%E7%A7%8B%E6%8B%9B/">秋招</a>
              </p>
              <p class="item-title">
                <a href="/2020/03/31/ge-ren-tu-xie-xi-lie-zong-jie-redis/" class="title">个人吐血系列-总结Redis</a>
              </p>
              <p class="item-date">
                <time datetime="2020-03-31T13:59:25.000Z" itemprop="datePublished">2020-03-31</time>
              </p>
            </div>
          </li>
          
          <li>
            
            <div class="item-inner">
              <p class="item-category">
                <a class="category-link" href="/categories/%E7%A7%8B%E6%8B%9B/">秋招</a>
              </p>
              <p class="item-title">
                <a href="/2020/03/30/ge-ren-tu-xie-xi-lie-zong-jie-mysql/" class="title">个人吐血系列-总结Mysql</a>
              </p>
              <p class="item-date">
                <time datetime="2020-03-30T07:13:08.000Z" itemprop="datePublished">2020-03-30</time>
              </p>
            </div>
          </li>
          
      </ul>
    </div>
  </div>
  

    
  </div>
</aside>

  
  
<aside class="sidebar sidebar-toc collapse" id="collapseToc" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    <nav id="toc" class="article-toc">
      <h3 class="toc-title">文章目录</h3>
      <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#创建数据库并制定编码"><span class="toc-number">1.</span> <span class="toc-text">创建数据库并制定编码</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#删除数据库"><span class="toc-number">2.</span> <span class="toc-text">删除数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#创建表"><span class="toc-number">3.</span> <span class="toc-text">创建表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#插入信息"><span class="toc-number">4.</span> <span class="toc-text">插入信息</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#查询信息"><span class="toc-number">5.</span> <span class="toc-text">查询信息</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#更新信息"><span class="toc-number">6.</span> <span class="toc-text">更新信息</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#删除信息"><span class="toc-number">7.</span> <span class="toc-text">删除信息</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#删除表"><span class="toc-number">8.</span> <span class="toc-text">删除表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#查看表信息"><span class="toc-number">9.</span> <span class="toc-text">查看表信息</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#一对多关系"><span class="toc-number">10.</span> <span class="toc-text">一对多关系</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#更改auto-increment"><span class="toc-number">11.</span> <span class="toc-text">更改auto_increment</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#常用sql语句"><span class="toc-number">12.</span> <span class="toc-text">常用sql语句</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#数据库"><span class="toc-number">12.1.</span> <span class="toc-text">数据库</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#表"><span class="toc-number">12.2.</span> <span class="toc-text">表</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#表的结构"><span class="toc-number">12.3.</span> <span class="toc-text">表的结构</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#表的数据"><span class="toc-number">12.4.</span> <span class="toc-text">表的数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#键"><span class="toc-number">12.5.</span> <span class="toc-text">键</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#视图"><span class="toc-number">12.6.</span> <span class="toc-text">视图</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#联接"><span class="toc-number">12.7.</span> <span class="toc-text">联接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#函数"><span class="toc-number">12.8.</span> <span class="toc-text">函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#用户"><span class="toc-number">12.9.</span> <span class="toc-text">用户</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#其他语句"><span class="toc-number">12.10.</span> <span class="toc-text">其他语句</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#小破站学习的mysql"><span class="toc-number">13.</span> <span class="toc-text">小破站学习的mysql</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#先贴sql"><span class="toc-number">13.1.</span> <span class="toc-text">先贴sql</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#基础查询"><span class="toc-number">14.</span> <span class="toc-text">基础查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#条件查询"><span class="toc-number">15.</span> <span class="toc-text">条件查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#排序查询"><span class="toc-number">16.</span> <span class="toc-text">排序查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#常见函数"><span class="toc-number">17.</span> <span class="toc-text">常见函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#分组函数"><span class="toc-number">18.</span> <span class="toc-text">分组函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#分组查询"><span class="toc-number">19.</span> <span class="toc-text">分组查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#连接查询"><span class="toc-number">20.</span> <span class="toc-text">连接查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#子查询"><span class="toc-number">21.</span> <span class="toc-text">子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#分页查询"><span class="toc-number">22.</span> <span class="toc-text">分页查询</span></a></li></ol>
    </nav>
  </div>
</aside>

<main class="main" role="main">
  <div class="content">
  <article id="post-常用的sql语句" class="article article-type-post" itemscope itemtype="http://schema.org/BlogPosting">
    
    <div class="article-header">
      
        
  
    <h1 class="article-title" itemprop="name">
      常用的sql语句
    </h1>
  

      
      <div class="article-meta">
        <span class="article-date">
    <i class="icon icon-calendar-check"></i>
	<a href="/2019/07/14/chang-yong-de-sql-yu-ju/" class="article-date">
	  <time datetime="2019-07-14T09:31:56.000Z" itemprop="datePublished">2019-07-14</time>
	</a>
</span>
        
  <span class="article-category">
    <i class="icon icon-folder"></i>
    <a class="article-category-link" href="/categories/db/">db</a>
  </span>

        
  <span class="article-tag">
    <i class="icon icon-tags"></i>
	<a class="article-tag-link" href="/tags/db/" rel="tag">db</a>
  </span>


        

        <span class="post-comment"><i class="icon icon-comment"></i> <a href="/2019/07/14/chang-yong-de-sql-yu-ju/#comments" class="article-comment-link">评论</a></span>
        
	
		<span class="post-wordcount hidden-xs" itemprop="wordCount">字数统计: 5.5k(字)</span>
	
	
		<span class="post-readcount hidden-xs" itemprop="timeRequired">阅读时长: 24(分)</span>
	

      </div>
    </div>
    <div class="article-entry marked-body" itemprop="articleBody">
      
        <h3 id="创建数据库并制定编码"><a href="#创建数据库并制定编码" class="headerlink" title="创建数据库并制定编码"></a>创建数据库并制定编码</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">Create</span> <span class="token keyword">database</span> 数据库名 <span class="token keyword">default</span> <span class="token keyword">character set</span> utf8<span class="token punctuation">;</span>
<span class="token keyword">Create</span> <span class="token keyword">database</span> ssm <span class="token keyword">default</span> <span class="token keyword">character set</span> utf8<span class="token punctuation">;</span></code></pre>
</li>
</ul>
<h3 id="删除数据库"><a href="#删除数据库" class="headerlink" title="删除数据库"></a>删除数据库</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">drop</span> <span class="token keyword">database</span> 数据名<span class="token punctuation">;</span>
<span class="token keyword">drop</span> <span class="token keyword">database</span> ssm<span class="token punctuation">;</span></code></pre>
<h3 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h3></li>
<li><p>一般情况：</p>
</li>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">Create</span> <span class="token keyword">table</span> 表名 <span class="token punctuation">(</span>
    列名 类型 约束 <span class="token keyword">auto_increment</span> <span class="token keyword">comment</span> <span class="token string">'备注'</span><span class="token punctuation">;</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> tablie flower <span class="token punctuation">(</span>
id <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token keyword">auto_increment</span> <span class="token keyword">comment</span> <span class="token string">'编号'</span><span class="token punctuation">,</span>
name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'花名'</span><span class="token punctuation">,</span>
price <span class="token keyword">float</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'价格'</span><span class="token punctuation">,</span>
production <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'原产地'</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> tablie flower <span class="token punctuation">(</span>
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">auto_increment</span> <span class="token keyword">comment</span> <span class="token string">'编号'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'花名'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>price<span class="token punctuation">`</span> <span class="token keyword">float</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'价格'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>production<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'原产地'</span><span class="token punctuation">,</span>
 <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
</li>
</ul>
<h3 id="插入信息"><a href="#插入信息" class="headerlink" title="插入信息"></a>插入信息</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">insert</span> <span class="token keyword">into</span> 表明<span class="token punctuation">(</span>姓名，性别，年龄<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">'李一'</span><span class="token punctuation">,</span> <span class="token string">'女'</span><span class="token punctuation">,</span> <span class="token string">'18'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token punctuation">`</span>flower<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>name<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>price<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>production<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">values</span> <span class="token punctuation">(</span>defalut<span class="token punctuation">,</span> <span class="token string">'矮牵牛'</span><span class="token punctuation">,</span> <span class="token number">2.5</span><span class="token punctuation">,</span> <span class="token string">'南美阿根廷'</span><span class="token punctuation">)</span></code></pre>
</li>
</ul>
<h3 id="查询信息"><a href="#查询信息" class="headerlink" title="查询信息"></a>查询信息</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> flower<span class="token punctuation">;</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> ID<span class="token operator">=</span><span class="token number">5</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> flower <span class="token keyword">where</span>  ID<span class="token operator">=</span><span class="token number">1</span></code></pre>
</li>
</ul>
<h3 id="更新信息"><a href="#更新信息" class="headerlink" title="更新信息"></a>更新信息</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">update</span> 表名 <span class="token keyword">set</span> name<span class="token operator">=</span><span class="token string">'李一'</span> <span class="token keyword">where</span> name<span class="token operator">=</span><span class="token string">'王五'</span><span class="token punctuation">;</span>
<span class="token keyword">update</span> flower <span class="token keyword">set</span> price<span class="token operator">=</span><span class="token number">1.2</span> <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
</li>
</ul>
<h3 id="删除信息"><a href="#删除信息" class="headerlink" title="删除信息"></a>删除信息</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">delete</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">delete</span> <span class="token keyword">from</span> flower <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
</li>
</ul>
<h3 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">drop</span> <span class="token keyword">table</span> 表名<span class="token punctuation">;</span>
<span class="token keyword">drop</span> <span class="token keyword">table</span> flower<span class="token punctuation">;</span></code></pre>
</li>
</ul>
<h3 id="查看表信息"><a href="#查看表信息" class="headerlink" title="查看表信息"></a>查看表信息</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">desc</span> 表名<span class="token punctuation">;</span>
<span class="token keyword">desc</span> flower<span class="token punctuation">;</span></code></pre>
</li>
</ul>
<h3 id="一对多关系"><a href="#一对多关系" class="headerlink" title="一对多关系"></a>一对多关系</h3><ul>
<li><p>可能是写的不熟练吧，所以要做个笔记，以免之后再次忘掉。</p>
</li>
<li><p>例子：假如有这样的一对多关系，首先有一个User模型，其中该模型有id、name和gender字段。还有一个Artical模型，其中该模型有id、title、content和user_id字段，而user_id则是外键。如下图：</p>
</li>
<li><p><img src="https://raw.githubusercontent.com/DreamCats/PicBed/master/20191121170733.png" alt=""></p>
</li>
<li><p>那么在mysql或者mysql的一些工具下创建输入以下代码：</p>
</li>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">create</span> <span class="token keyword">database</span> demo <span class="token keyword">default</span> <span class="token keyword">character set</span> utf8<span class="token punctuation">;</span> <span class="token comment" spellcheck="true">-- 创建数据库</span>

<span class="token keyword">use</span> demo<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">-- 创建user表格</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> <span class="token keyword">user</span> <span class="token punctuation">(</span>
    <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">auto_increment</span> <span class="token keyword">comment</span> <span class="token string">'编号'</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'姓名'</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>gender<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'性别'</span><span class="token punctuation">,</span>
    <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">-- 创建artical</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> artical <span class="token punctuation">(</span>
    <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">auto_increment</span> <span class="token keyword">comment</span> <span class="token string">'文章编号'</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>title<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'标题'</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>content<span class="token punctuation">`</span> <span class="token keyword">text</span> <span class="token keyword">default</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'内容'</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>user_id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'用户编号'</span><span class="token punctuation">,</span>
    <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">key</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>user_id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">constraint</span> <span class="token punctuation">`</span>artical_idfk_1<span class="token punctuation">`</span> <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>user_id<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">references</span> <span class="token punctuation">`</span><span class="token keyword">user</span><span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token comment" spellcheck="true">-- 这句话非常重要 constraint ··· 约束</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>    

<span class="token comment" spellcheck="true">-- 在user中插入数据</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">user</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>name<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>gender<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token keyword">default</span><span class="token punctuation">,</span> <span class="token string">'Maifeng'</span><span class="token punctuation">,</span> <span class="token string">'男'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token keyword">user</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>name<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>gender<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token keyword">default</span><span class="token punctuation">,</span> <span class="token string">'Liumeng'</span><span class="token punctuation">,</span> <span class="token string">'女'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">-- 在artical中插入数据</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token punctuation">`</span>artical<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>title<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>content<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>user_id<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token keyword">default</span><span class="token punctuation">,</span> <span class="token string">'我是mm'</span><span class="token punctuation">,</span> <span class="token string">'null'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> <span class="token punctuation">`</span>artical<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>title<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>content<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>user_id<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">values</span> <span class="token punctuation">(</span><span class="token keyword">default</span><span class="token punctuation">,</span> <span class="token string">'我是xxx'</span><span class="token punctuation">,</span> <span class="token string">'null'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
</li>
<li><p>如下图user和artical表</p>
</li>
<li><p><img src="https://raw.githubusercontent.com/DreamCats/PicBed/master/20191121170817.png" alt=""></p>
</li>
<li><p><img src="https://raw.githubusercontent.com/DreamCats/PicBed/master/20191121170856.png" alt=""></p>
</li>
</ul>
<h3 id="更改auto-increment"><a href="#更改auto-increment" class="headerlink" title="更改auto_increment"></a>更改auto_increment</h3><ul>
<li><pre class=" language-sql"><code class="language-sql"><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">auto_increment</span><span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> flower <span class="token keyword">auto_increment</span><span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span></code></pre>
</li>
<li></li>
</ul>
<h3 id="常用sql语句"><a href="#常用sql语句" class="headerlink" title="常用sql语句"></a>常用sql语句</h3><h4 id="数据库"><a href="#数据库" class="headerlink" title="数据库"></a>数据库</h4><pre class=" language-mysql"><code class="language-mysql"># 查看所有数据库
show databases;

# 创建一个数据库
create database demo;

# 删除一个数据库
drop dababase demo;

# 使用这个数据库
use demo;
</code></pre>
<h4 id="表"><a href="#表" class="headerlink" title="表"></a>表</h4><pre class=" language-mysql"><code class="language-mysql"># 查看所有的表
show tables;

# 创建一个表
create table n(`id` int, `name` varchar(10));
create table m(`id` int, `name` varchar(10), primary key(`id`), foreign key(`id`) references n(`id`), unique);
create table m(`id` int, `name` varchar(10));

# 直接将查询结果导入或复制到新创建的表
create table n select * from m;

# 将创建的表与一个存在的表的数据结构类似
create table m like n;

# 创建一个临时表
# 临时表将在你连接MySQL期间存在。当断开连接时，MySQL将自动删除表并释放所用的空间。也可手动删除。
create temporary table l(`id` int, name varchar(10));
# 直接将查询结果导入或复制到新的临时表
create temporary table tt select * from n;

# 删除一个已经存在的表
drop table if exists m;

# 更改存在表的名称
alter table n rename m;
rename table n to m;

# 查看表的结构(5种,效果相同)
desc n;
describe n;
show columns in n;
show columns from n;
explain n;
# 查看表的创建语句
show create table n;</code></pre>
<h4 id="表的结构"><a href="#表的结构" class="headerlink" title="表的结构"></a>表的结构</h4><pre class=" language-mysql"><code class="language-mysql"># 添加字段
alter table n add age varchar(2);
# 删除字段
alter table n drop age;
# 更改字段属性和属性
alter table n change age a int;
# 只更改字段属性
alter table n modify age varchar(7);</code></pre>
<h4 id="表的数据"><a href="#表的数据" class="headerlink" title="表的数据"></a>表的数据</h4><pre class=" language-mysql"><code class="language-mysql"># 增加数据
insert into n values(1, 'tom', '23'), (2, 'john', '22');
insert into n select * from n; # 把数据复制一遍重新插入
# 删除数据
delete from n where id = 2;
# 更改数据
update n set name = 'tom' where id = 2;
# 数据查找
select * from n where name like '%h%';
# 数据排序(反序)
select * from n order by name, id desc;</code></pre>
<h4 id="键"><a href="#键" class="headerlink" title="键"></a>键</h4><pre class=" language-mysql"><code class="language-mysql"># 添加主键
alter table n add primary key(id);
# 删除主键
alter table n drop primary key;
# 添加外键
alter table m add foreign key(id) references n(id); # 自动生成键名m_ibfk_1
# 自定义名称的外键
alter table m add constraint fk_id foreign key(id) references n(id);
# 删除外键
alter table m drop foreign key `fk_id`;
# 修改外键
alter table m drop foreign key `fk_id`, add constraint fk_id2 foreign key(id) references n(id);
# 添加唯一键
alter table n add unique (name);
alter table n add unique u_name (name);
alter table n add unique index u_name (name);
alter table n add constraint u_name unique (name);
create unique index u_name on n(name);
# 添加索引
alter table n add index (age);
alter table n add index i_age (age);
create index i_age on n(age);

# 删除索引或唯一键
drop index u_name on n;
drop index i_age on n;</code></pre>
<h4 id="视图"><a href="#视图" class="headerlink" title="视图"></a>视图</h4><pre class=" language-mysql"><code class="language-mysql"># 创建视图
create view v as select id, name from n;
create view v(id, name) as select id, name from n;
# 查看视图(与表操作类似)
select * from v;
desc v;
# 查看视图语句
show create view v;
# 更改视图
CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
ALTER VIEW v AS SELECT name FROM n;
# 删除视图
drop view if exists v;</code></pre>
<h4 id="联接"><a href="#联接" class="headerlink" title="联接"></a>联接</h4><pre class=" language-mysql"><code class="language-mysql"># 内联接
select * from m inner join n on m.id = n.id;
# 左外连接
select * from m left join n on m.id = n.id;
# 右外连接
select * from m right join n on m.id = n.id;
# 交叉连接
select * from m  cross join n; # 标准写法
select * from m,n;
# 类似于全连接full join 的联接用法
select id, name from m
union
select id, name from n</code></pre>
<h4 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h4><pre class=" language-mysql"><code class="language-mysql"># 聚合函数
select count(id) as total from n; # 总数
select sum(age) as all_age from n; # 总和
select avg(age) as all_age from n; # 平均值
select max(age) as all_age from n; # 最大值
select min(age) as all_age from n; # 最小值
# 数学函数
select abs(-5); # 绝对值
select bin(15), oct(15), hex(15); # 二进制，八进制，十六进制
SELECT pi();   # 圆周率3.141593
SELECT ceil(5.5);   # 大于x的最小整数值6
SELECT floor(5.5);   # 小于x的最大整数值5
SELECT greatest(3,1,4,1,5,9,2,6);   # 返回集合中最大的值9
SELECT least(3,1,4,1,5,9,2,6);    # 返回集合中最小的值1
SELECT mod(5,3);    # 余数2
SELECT rand();    # 返回０到１内的随机值，每次不一样
SELECT rand(5);   # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
SELECT round(1415.1415);   # 四舍五入1415
SELECT round(1415.1415, 3);   # 四舍五入三位数1415.142
SELECT round(1415.1415, -1);    # 四舍五入整数位数1420
SELECT truncate(1415.1415, 3);    # 截短为3位小数1415.141
SELECT truncate(1415.1415, -1);   # 截短为-1位小数1410
SELECT sign(-5);    # 符号的值负数-1
SELECT sign(5);    # 符号的值正数1
SELECT sqrt(9);   # 平方根3
SELECT sqrt(9);   # 平方根3
# 字符串函数
SELECT concat('a', 'p', 'p', 'le');   # 连接字符串-apple
SELECT concat_ws(',', 'a', 'p', 'p', 'le');   # 连接用','分割字符串-a,p,p,le
SELECT insert('chinese', 3, 2, 'IN');    # 将字符串'chinese'从3位置开始的2个字符替换为'IN'-chINese
SELECT left('chinese', 4);   # 返回字符串'chinese'左边的4个字符-chin
SELECT right('chinese', 3);   # 返回字符串'chinese'右边的3个字符-ese

SELECT substring('chinese', 3);   # 返回字符串'chinese'第三个字符之后的子字符串-inese
SELECT substring('chinese', -3);   # 返回字符串'chinese'倒数第三个字符之后的子字符串-ese
SELECT substring('chinese', 3, 2);   # 返回字符串'chinese'第三个字符之后的两个字符-in
SELECT trim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese'
SELECT ltrim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese '
SELECT rtrim(' chinese ');    # 切割字符串' chinese '两边的空字符-' chinese'
SELECT repeat('boy', 3);    # 重复字符'boy'三次-'boyboyboy'
SELECT reverse('chinese');    # 反向排序-'esenihc'
SELECT length('chinese');   # 返回字符串的长度-7
SELECT upper('chINese'), lower('chINese');    # 大写小写 CHINESE    chinese
SELECT ucase('chINese'), lcase('chINese');    # 大写小写 CHINESE    chinese
SELECT position('i' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-3
SELECT position('e' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-5
SELECT strcmp('abc', 'abd');    # 比较字符串，第一个参数小于第二个返回负数- -1
SELECT strcmp('abc', 'abb');    # 比较字符串，第一个参数大于第二个返回正数- 1
# 时间函数
SELECT current_date, current_time, now();    # 2018-01-13   12:33:43    2018-01-13 12:33:43
SELECT hour(current_time), minute(current_time), second(current_time);    # 12  31   34
SELECT year(current_date), month(current_date), week(current_date);   # 2018    1   1
SELECT quarter(current_date);   # 1
SELECT monthname(current_date), dayname(current_date);   # January  Saturday
SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);    # 7   13  13
# 控制流函数
SELECT if(3>2, 't', 'f'), if(3<2, 't', 'f');    # t f
SELECT ifnull(NULL, 't'), ifnull(2, 't');    # t 2
SELECT isnull(1), isnull(1/0);    # 0 1 是null返回1，不是null返回0
SELECT nullif('a', 'a'), nullif('a', 'b');    # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
SELECT CASE 2
       WHEN 1 THEN 'first'
       WHEN 2 THEN 'second'
       WHEN 3 THEN 'third'
       ELSE 'other'
       END ;     # second
# 系统信息函数
SELECT database();    # 当前数据库名-test
SELECT connection_id();   # 当前用户id-306
SELECT user();    # 当前用户-root@localhost
SELECT version();   # 当前mysql版本
SELECT found_rows();    # 返回上次查询的检索行数</code></pre>
<h4 id="用户"><a href="#用户" class="headerlink" title="用户"></a>用户</h4><pre class=" language-mysql"><code class="language-mysql"># 增加用户
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test'));    # 在用户表中插入用户信息，直接操作User表不推荐
# 删除用户
DROP USER 'test'@'localhost';
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 用户授权
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
# 授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
FLUSH PRIVILEGES ;   # 刷新系统权限表,使授予权限生效
# 撤销用户授权
REVOKE DELETE ON *.* FROM 'test'@'localhost';   # 取消该用户的删除权限</code></pre>
<h4 id="其他语句"><a href="#其他语句" class="headerlink" title="其他语句"></a>其他语句</h4><pre class=" language-mysql"><code class="language-mysql"># 查看所有的表信息（包括视图）
SHOW TABLE STATUS;</code></pre>
<h3 id="小破站学习的mysql"><a href="#小破站学习的mysql" class="headerlink" title="小破站学习的mysql"></a>小破站学习的mysql</h3><ul>
<li><a href="https://www.bilibili.com/video/av49181542?p=1" target="_blank" rel="noopener">美女老师</a></li>
<li><a href="https://blog.csdn.net/GongmissYan/article/details/102937816" target="_blank" rel="noopener">sql表</a></li>
</ul>
<h4 id="先贴sql"><a href="#先贴sql" class="headerlink" title="先贴sql"></a>先贴sql</h4><pre class=" language-sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span> <span class="token punctuation">(</span>
  <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> unsigned <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span> <span class="token keyword">COMMENT</span> <span class="token string">'账户'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>password<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_general_ci <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span> <span class="token keyword">COMMENT</span> <span class="token string">'密码'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>gender<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_general_ci <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">8</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4 <span class="token keyword">COLLATE</span><span class="token operator">=</span>utf8mb4_general_ci<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>password<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>gender<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>age<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token keyword">VALUES</span>
    <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token string">'123'</span><span class="token punctuation">,</span> <span class="token string">'0'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token string">'123'</span><span class="token punctuation">,</span> <span class="token string">'0'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token string">'123'</span><span class="token punctuation">,</span> <span class="token string">'1'</span><span class="token punctuation">,</span> <span class="token number">21</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token string">'123'</span><span class="token punctuation">,</span> <span class="token string">'0'</span><span class="token punctuation">,</span> <span class="token number">22</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token string">'dae'</span><span class="token punctuation">,</span> <span class="token string">'123'</span><span class="token punctuation">,</span> <span class="token string">'1'</span><span class="token punctuation">,</span> <span class="token number">23</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token string">'e'</span><span class="token punctuation">,</span> <span class="token string">'123'</span><span class="token punctuation">,</span> <span class="token string">'0'</span><span class="token punctuation">,</span> <span class="token number">24</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span> <span class="token string">'f'</span><span class="token punctuation">,</span> <span class="token string">'123'</span><span class="token punctuation">,</span> <span class="token string">'0'</span><span class="token punctuation">,</span> <span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre>
<h3 id="基础查询"><a href="#基础查询" class="headerlink" title="基础查询"></a>基础查询</h3><pre class=" language-sql"><code class="language-sql"><span class="token comment" spellcheck="true">/*
语法：
    select 查询列表 from 表明；
特点：
    1. 查询列表可以是：表中的字段、常量值、表达式、函数
    2. 查询的结构是一个虚拟的表格
*/</span>
 <span class="token comment" spellcheck="true">/*
 别名
 ①便于理解
 ②如果要查询的字段有重名的情况，使用别名可以区分开来

 */</span>
 <span class="token comment" spellcheck="true"># +号的作用</span>
 <span class="token comment" spellcheck="true">/*
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
            如果转换成功，则继续做加法运算
select 'john'+90;    如果转换失败，则将字符型数值转换成0

select null+10; 只要其中一方为null，则结果肯定为null
 */</span>    
<span class="token comment" spellcheck="true"># 题目</span>

<span class="token comment" spellcheck="true">#1.    下面的语句是否可以执行成功  :能</span>
 <span class="token keyword">SELECT</span>
    last_name <span class="token punctuation">,</span>
    job_id <span class="token punctuation">,</span>
    salary <span class="token keyword">AS</span> sal
<span class="token keyword">FROM</span>
    employees<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#2.下面的语句是否可以执行成功  ：能</span>
 <span class="token keyword">SELECT</span>
    <span class="token operator">*</span>
<span class="token keyword">FROM</span>
    employees<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#3.找出下面语句中的错误  ：有个中文标点符号</span>
 <span class="token keyword">SELECT</span>
    employee_id <span class="token punctuation">,</span>
    last_name<span class="token punctuation">,</span>
    salary <span class="token operator">*</span> <span class="token number">12</span> <span class="token keyword">AS</span> <span class="token string">"ANNUAL  SALARY"</span>
<span class="token keyword">FROM</span>
    employees<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#4.显示表departments的结构，并查询其中的全部数据</span>
<span class="token keyword">DESC</span> departments <span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> departments<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#5.显示出表employees中的全部job_id（不能重复）</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> job_id <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
</code></pre>
<h3 id="条件查询"><a href="#条件查询" class="headerlink" title="条件查询"></a>条件查询</h3><pre class=" language-sql"><code class="language-sql"><span class="token comment" spellcheck="true"># 2: 条件查询</span>
<span class="token comment" spellcheck="true">/*

语法：
    select 
        查询列表
    from
        表名
    where
        筛选条件;

分类：
    一、按条件表达式筛选

    简单条件运算符：> &lt; = != &lt;> >= &lt;=

    二、按逻辑表达式筛选
    逻辑运算符：
    作用：用于连接条件表达式
        &amp;&amp; || !
        and or not

    &amp;&amp;和and：两个条件都为true，结果为true，反之为false
    ||或or： 只要有一个条件为true，结果为true，反之为false
    !或not： 如果连接的条件本身为false，结果为true，反之为false

    三、模糊查询
        like
        between and
        in
        is null
*/</span>
<span class="token comment" spellcheck="true"># 1. 查询年龄大于22的用户信息</span>
<span class="token keyword">SELECT</span>
    <span class="token operator">*</span>
<span class="token keyword">FROM</span>
    <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span>
<span class="token keyword">WHERE</span>
    <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token operator">></span> <span class="token number">22</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># 2. 查询年龄不等于22的用户和密码</span>
<span class="token keyword">SELECT</span>
    <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>password<span class="token punctuation">`</span>
<span class="token keyword">FROM</span>
    <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span>
<span class="token keyword">WHERE</span>
    <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token operator">&lt;></span> <span class="token number">20</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># 查询年龄在21到23之间的用户名和密码</span>
<span class="token keyword">SELECT</span>
    <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>password<span class="token punctuation">`</span>
<span class="token keyword">FROM</span> 
    <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span>
<span class="token keyword">WHERE</span>
    <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token operator">>=</span><span class="token number">21</span> <span class="token operator">AND</span> <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token operator">&lt;=</span> <span class="token number">23</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span>
    <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>password<span class="token punctuation">`</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>age<span class="token punctuation">`</span>
<span class="token keyword">FROM</span> 
    <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span>
<span class="token keyword">WHERE</span>
    <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token operator">BETWEEN</span> <span class="token number">21</span> <span class="token operator">AND</span> <span class="token number">23</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># 查询年龄21和23的用户名和密码</span>
<span class="token keyword">SELECT</span>
    <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>password<span class="token punctuation">`</span>
<span class="token keyword">FROM</span> 
    <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span>
<span class="token keyword">WHERE</span>
    <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token operator">IN</span><span class="token punctuation">(</span><span class="token number">21</span><span class="token punctuation">,</span> <span class="token number">23</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># 模糊查询 like</span>
<span class="token comment" spellcheck="true">/*
特点：
①一般和通配符搭配使用
    通配符：
    % 任意多个字符,包含0个字符
    _ 任意单个字符
*/</span>
<span class="token comment" spellcheck="true"># 查询用户名包字符a的用户信息</span>
<span class="token keyword">SELECT</span> 
    <span class="token operator">*</span>
<span class="token keyword">FROM</span> 
    <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span>
<span class="token keyword">WHERE</span> 
    <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span> <span class="token operator">LIKE</span> <span class="token string">'%a%'</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> 
    <span class="token operator">*</span>
<span class="token keyword">FROM</span> 
    <span class="token punctuation">`</span>t_user<span class="token punctuation">`</span>
<span class="token keyword">WHERE</span> 
    <span class="token punctuation">`</span>user_name<span class="token punctuation">`</span> <span class="token operator">LIKE</span> <span class="token string">'_a%'</span><span class="token punctuation">;</span>
</code></pre>
<h3 id="排序查询"><a href="#排序查询" class="headerlink" title="排序查询"></a>排序查询</h3><pre class=" language-sql"><code class="language-sql"><span class="token comment" spellcheck="true">/*
排序
    select 查询列表
    from 表
    order by 排序列表 asc|desc. asc:升序，desc降序    
            可以放单个字段，也可以放多个字段，可以表达式，也可以放函数
            order by 放最后 除limit语句
*/</span>    
题目：
<span class="token comment" spellcheck="true">#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序</span>
<span class="token keyword">SELECT</span> 
    last_name<span class="token punctuation">,</span>
    department_id<span class="token punctuation">,</span>
    salary<span class="token operator">*</span><span class="token number">12</span><span class="token operator">*</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token operator">+</span>IFNULL<span class="token punctuation">(</span>commission_pct<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 年薪 
<span class="token keyword">FROM</span> 
    employees
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 年薪 <span class="token keyword">DESC</span><span class="token punctuation">,</span> last_name <span class="token keyword">ASC</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序</span>
<span class="token keyword">SELECT</span> 
    last_name <span class="token punctuation">,</span>
    salary 
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> 
    salary <span class="token operator">NOT</span> <span class="token operator">BETWEEN</span> <span class="token number">8000</span> <span class="token operator">AND</span> <span class="token number">17000</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span> <span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序</span>
<span class="token keyword">SELECT</span> 
    <span class="token operator">*</span><span class="token punctuation">,</span>LENGTH<span class="token punctuation">(</span>email<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> email <span class="token operator">LIKE</span> <span class="token string">'%e%'</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> LENGTH<span class="token punctuation">(</span>email<span class="token punctuation">)</span> <span class="token keyword">DESC</span><span class="token punctuation">,</span>department_id <span class="token keyword">ASC</span><span class="token punctuation">;</span>

</code></pre>
<h3 id="常见函数"><a href="#常见函数" class="headerlink" title="常见函数"></a>常见函数</h3><pre class=" language-sql"><code class="language-sql"><span class="token comment" spellcheck="true">/*

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节  2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
    ①叫什么（函数名）
    ②干什么（函数功能）

分类：
    1、单行函数
    如 concat、length、ifnull等
    2、分组函数

    功能：做统计使用，又称为统计函数、聚合函数、组函数

常见函数：
    一、单行函数
    字符函数：
    length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
    concat
    substr
    instr
    trim
    upper
    lower
    lpad
    rpad
    replace

    数学函数：
    round
    ceil
    floor
    truncate
    mod

    日期函数：
    now
    curdate
    curtime
    year
    month
    monthname
    day
    hour
    minute
    second
    str_to_date
    date_format
    其他函数：
    version
    database
    user
    控制函数
    if
    case
*/</span>
<span class="token comment" spellcheck="true"># 1. 字符函数</span>
<span class="token keyword">SELECT</span> LENGTH<span class="token punctuation">(</span><span class="token string">'join'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token keyword">SELECT</span> LENGTH<span class="token punctuation">(</span><span class="token string">'张三丰hahaha'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%char%'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true"># 2. 拼接字符串</span>
<span class="token keyword">SELECT</span> CONCAT<span class="token punctuation">(</span><span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token string">'_'</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>password<span class="token punctuation">`</span><span class="token punctuation">)</span> infos <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#3.upper、lower</span>
<span class="token keyword">SELECT</span> UPPER<span class="token punctuation">(</span><span class="token string">'john'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> LOWER<span class="token punctuation">(</span><span class="token string">'joHn'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#示例：将姓变大写，名变小写，然后拼接</span>
<span class="token keyword">SELECT</span> CONCAT<span class="token punctuation">(</span>UPPER<span class="token punctuation">(</span><span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span> LOWER<span class="token punctuation">(</span><span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">)</span> infos <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#4.substr、substring</span>
<span class="token comment" spellcheck="true">#注意：索引从1开始</span>
<span class="token comment" spellcheck="true">#截取从指定索引处后面所有字符</span>
<span class="token keyword">SELECT</span> SUBSTR<span class="token punctuation">(</span><span class="token string">'我是dreamcat'</span><span class="token punctuation">,</span><span class="token number">7</span><span class="token punctuation">)</span> out_put<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#截取从指定索引处指定字符长度的字符</span>
<span class="token keyword">SELECT</span> SUBSTR<span class="token punctuation">(</span><span class="token string">'我是dreamcat'</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> out_put<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> 
CONCAT<span class="token punctuation">(</span>UPPER<span class="token punctuation">(</span>SUBSTR<span class="token punctuation">(</span><span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'_'</span><span class="token punctuation">,</span> LOWER<span class="token punctuation">(</span>SUBSTR<span class="token punctuation">(</span><span class="token punctuation">`</span>user_name<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span> out_put 
<span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#5.instr 返回子串第一次出现的索引，如果找不到返回0</span>
<span class="token keyword">SELECT</span> INSTR<span class="token punctuation">(</span><span class="token string">'我是dreamcat'</span><span class="token punctuation">,</span> <span class="token string">'dream'</span><span class="token punctuation">)</span> out_put<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#6.trim</span>
<span class="token keyword">SELECT</span> 
LENGTH<span class="token punctuation">(</span>
    TRIM<span class="token punctuation">(</span>
        <span class="token string">'   dreamcat.  '</span>
    <span class="token punctuation">)</span>
<span class="token punctuation">)</span> out_put<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> TRIM<span class="token punctuation">(</span><span class="token string">'aa'</span> <span class="token keyword">FROM</span> <span class="token string">'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'</span><span class="token punctuation">)</span>  <span class="token keyword">AS</span> out_put<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#7.lpad 用指定的字符实现左填充指定长度</span>
<span class="token keyword">SELECT</span> LPAD<span class="token punctuation">(</span><span class="token string">'殷素素'</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token string">'*'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> out_put<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#8.rpad 用指定的字符实现右填充指定长度</span>
<span class="token keyword">SELECT</span> RPAD<span class="token punctuation">(</span><span class="token string">'殷素素'</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">,</span><span class="token string">'ab'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> out_put<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#9.replace 替换</span>
<span class="token keyword">SELECT</span> REPLACE<span class="token punctuation">(</span><span class="token string">'周芷若周芷若周芷若周芷若张无忌爱上了周芷若'</span><span class="token punctuation">,</span><span class="token string">'周芷若'</span><span class="token punctuation">,</span><span class="token string">'赵敏'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> out_put<span class="token punctuation">;</span>


<span class="token comment" spellcheck="true">#二、数学函数</span>
<span class="token comment" spellcheck="true">#round 四舍五入</span>
<span class="token keyword">SELECT</span> <span class="token function">ROUND</span><span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1.55</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">ROUND</span><span class="token punctuation">(</span><span class="token number">1.567</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#ceil 向上取整,返回>=该参数的最小整数</span>
<span class="token keyword">SELECT</span> CEIL<span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1.02</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> CEIL<span class="token punctuation">(</span><span class="token number">1.02</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#floor 向下取整，返回&lt;=该参数的最大整数</span>
<span class="token keyword">SELECT</span> FLOOR<span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">9.99</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> FLOOR<span class="token punctuation">(</span><span class="token number">9.99</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#truncate 截断</span>
<span class="token keyword">SELECT</span> <span class="token keyword">TRUNCATE</span><span class="token punctuation">(</span><span class="token number">1.69999</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#mod取余</span>
<span class="token keyword">SELECT</span> MOD<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token number">10</span><span class="token operator">%</span><span class="token number">3</span><span class="token punctuation">;</span>


<span class="token comment" spellcheck="true">#三、日期函数</span>
<span class="token comment" spellcheck="true">#now 返回当前系统日期+时间</span>
<span class="token keyword">SELECT</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#curdate 返回当前系统日期，不包含时间</span>
<span class="token keyword">SELECT</span> CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#curtime 返回当前时间，不包含日期</span>
<span class="token keyword">SELECT</span> CURTIME<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#可以获取指定的部分，年、月、日、小时、分钟、秒</span>
<span class="token keyword">SELECT</span> YEAR<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 年<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> YEAR<span class="token punctuation">(</span><span class="token string">'1998-1-1'</span><span class="token punctuation">)</span> 年<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> MONTH<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 月<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> MONTHNAME<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 月<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#str_to_date 将字符通过指定的格式转换成日期</span>
<span class="token keyword">SELECT</span> STR_TO_DATE<span class="token punctuation">(</span><span class="token string">'1998-3-2'</span><span class="token punctuation">,</span><span class="token string">'%Y-%c-%d'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> out_put<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#date_format 将日期转换成字符</span>
<span class="token keyword">SELECT</span> DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'%y年%m月%d日'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> out_put<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#四、其他函数</span>

<span class="token keyword">SELECT</span> VERSION<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DATABASE</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">USER</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#五、流程控制函数</span>
<span class="token comment" spellcheck="true">#1.if函数： if else 的效果</span>

<span class="token keyword">SELECT</span> <span class="token keyword">IF</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token operator">&lt;</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token string">'大'</span><span class="token punctuation">,</span><span class="token string">'小'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> 
    <span class="token operator">*</span><span class="token punctuation">,</span>
<span class="token keyword">IF</span><span class="token punctuation">(</span><span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token operator">&lt;</span> <span class="token number">24</span><span class="token punctuation">,</span> <span class="token string">'young'</span><span class="token punctuation">,</span> <span class="token string">'old'</span><span class="token punctuation">)</span> <span class="token string">'new age'</span>
<span class="token keyword">FROM</span> 
    t_user<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#2.case函数的使用一： switch case 的效果</span>
<span class="token comment" spellcheck="true">/*
java中
switch(变量或表达式){
    case 常量1：语句1;break;
    ...
    default:语句n;break;


}

mysql中

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/</span>

<span class="token keyword">SELECT</span> 
    <span class="token operator">*</span><span class="token punctuation">,</span>
<span class="token keyword">CASE</span> <span class="token punctuation">`</span>age<span class="token punctuation">`</span>
<span class="token keyword">WHEN</span> <span class="token number">21</span> <span class="token keyword">THEN</span> <span class="token number">21</span><span class="token operator">+</span><span class="token number">1</span>
<span class="token keyword">WHEN</span> <span class="token number">22</span> <span class="token keyword">THEN</span> <span class="token number">22</span><span class="token operator">+</span><span class="token number">2</span>
<span class="token keyword">WHEN</span> <span class="token number">23</span> <span class="token keyword">THEN</span> <span class="token number">23</span><span class="token operator">+</span><span class="token number">3</span>
<span class="token keyword">ELSE</span> age
<span class="token keyword">END</span> <span class="token string">'NEW age'</span>
<span class="token keyword">FROM</span>
    t_user<span class="token punctuation">;</span>
题目：
<span class="token comment" spellcheck="true">#1.    显示系统时间(注：日期+时间)</span>
<span class="token keyword">SELECT</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#2.    查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）</span>
<span class="token keyword">SELECT</span> 
    employee_id <span class="token punctuation">,</span>
    last_name <span class="token punctuation">,</span>
    salary <span class="token punctuation">,</span>
    salary <span class="token operator">*</span> <span class="token number">1.2</span> <span class="token string">'new salary'</span>
<span class="token keyword">FROM</span>  employees<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#3.    将员工的姓名按首字母排序，并写出姓名的长度（length）</span>
<span class="token keyword">SELECT</span> 
    LENGTH <span class="token punctuation">(</span>last_name<span class="token punctuation">)</span> 长度<span class="token punctuation">,</span> SUBSTR<span class="token punctuation">(</span>last_name<span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span> 首字母<span class="token punctuation">,</span> last_name
<span class="token keyword">FROM</span> employees
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 首字母<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#4.    做一个查询，产生下面的结果</span>
<span class="token comment" spellcheck="true">#&lt;last_name> earns &lt;salary> monthly but wants &lt;salary*3></span>
<span class="token comment" spellcheck="true">#Dream Salary</span>
<span class="token comment" spellcheck="true">#King earns 24000 monthly but wants 72000</span>
<span class="token keyword">SELECT</span> 
    CONCAT<span class="token punctuation">(</span>last_name<span class="token punctuation">,</span> <span class="token string">' earns '</span><span class="token punctuation">,</span> salary<span class="token punctuation">,</span> <span class="token string">' monthly but wants '</span><span class="token punctuation">,</span> salary <span class="token operator">*</span> <span class="token number">3</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'Dream Salary'</span>
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> salary <span class="token operator">=</span> <span class="token number">24000</span><span class="token punctuation">;</span>
</code></pre>
<h3 id="分组函数"><a href="#分组函数" class="headerlink" title="分组函数"></a>分组函数</h3><pre class=" language-sql"><code class="language-sql"><span class="token comment" spellcheck="true">#二、分组函数</span>
<span class="token comment" spellcheck="true">/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：
1、sum、avg一般用于处理数值型
   max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

*/</span>
<span class="token comment" spellcheck="true">#1、简单 的使用</span>
<span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span> 
<span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>user_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>user_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>user_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span> 

<span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> password <span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> password<span class="token punctuation">)</span> <span class="token keyword">FROM</span>  t_user<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t_user<span class="token punctuation">;</span></code></pre>
<h3 id="分组查询"><a href="#分组查询" class="headerlink" title="分组查询"></a>分组查询</h3><pre class=" language-sql"><code class="language-sql">
<span class="token comment" spellcheck="true">#进阶5：分组查询</span>
 <span class="token comment" spellcheck="true">/*
语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
        针对的表            位置        连接的关键字
分组前筛选    原始表                group by前    where

分组后筛选    group by后的结果集            group by后    having

问题1：分组函数做筛选能不能放在where后面
答：不能

问题2：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用
*/</span>
<span class="token keyword">SELECT</span>
    <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span> 
    t_user
<span class="token keyword">WHERE</span> 
    password <span class="token operator">=</span> <span class="token string">'123'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> 
    <span class="token function">AVG</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span><span class="token punctuation">,</span>
    password
<span class="token keyword">FROM</span> 
    t_user
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> password<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> 
    <span class="token function">AVG</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span><span class="token punctuation">,</span>
    gender 
<span class="token keyword">FROM</span> 
    t_user
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> gender <span class="token punctuation">;</span>
题目：
<span class="token comment" spellcheck="true">#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序</span>
<span class="token keyword">SELECT</span> 
    <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token function">SUM</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> job_id 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> job_id <span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#2.查询员工最高工资和最低工资的差距（DIFFERENCE）</span>
<span class="token keyword">SELECT</span> 
    <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">AS</span>  DIFFERENCE
<span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内</span>

<span class="token keyword">SELECT</span> 
    <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span> manager_id 
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> manager_id <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> manager_id
<span class="token keyword">HAVING</span> <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token operator">>=</span> <span class="token number">6000</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序</span>


<span class="token keyword">SELECT</span> 
    department_id <span class="token punctuation">,</span>
    <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> 
    employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">DESC</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#5.选择具有各个job_id的员工人数</span>

<span class="token keyword">SELECT</span> 
    <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">,</span> 
    job_id 
<span class="token keyword">FROM</span> 
    employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> job_id<span class="token punctuation">;</span>    </code></pre>
<h3 id="连接查询"><a href="#连接查询" class="headerlink" title="连接查询"></a>连接查询</h3><pre class=" language-sql"><code class="language-sql">
<span class="token comment" spellcheck="true">#进阶6：连接查询</span>
 <span class="token comment" spellcheck="true">/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

    按年代分类：
    sql92标准:仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接

        交叉连接
*/</span>
<span class="token comment" spellcheck="true">#1.显示所有员工的姓名，部门号和部门名称。</span>
 <span class="token keyword">SELECT</span>
    <span class="token number">e</span><span class="token punctuation">.</span>last_name <span class="token punctuation">,</span>
    <span class="token number">d</span><span class="token punctuation">.</span>department_id <span class="token punctuation">,</span>
    <span class="token number">d</span><span class="token punctuation">.</span>department_name
<span class="token keyword">FROM</span>
    employees <span class="token number">e</span> <span class="token punctuation">,</span>
    departments <span class="token number">d</span>
<span class="token keyword">WHERE</span>
    <span class="token number">e</span><span class="token punctuation">.</span>department_id <span class="token operator">=</span> <span class="token number">d</span><span class="token punctuation">.</span>department_id <span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#2.查询90号部门员工的job_id和90号部门的location_id</span>
 <span class="token keyword">SELECT</span>
    <span class="token number">e</span><span class="token punctuation">.</span>job_id <span class="token punctuation">,</span>
    <span class="token number">d</span><span class="token punctuation">.</span>location_id
<span class="token keyword">FROM</span>
    employees <span class="token number">e</span> <span class="token punctuation">,</span>
    departments <span class="token number">d</span>
<span class="token keyword">WHERE</span>
    <span class="token number">e</span><span class="token punctuation">.</span>department_id <span class="token operator">=</span> <span class="token number">d</span><span class="token punctuation">.</span>department_id
    <span class="token operator">AND</span> <span class="token number">e</span><span class="token punctuation">.</span>department_id <span class="token operator">=</span> <span class="token number">90</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#3.    选择所有有奖金的员工的 last_name , department_name , location_id , city</span>
 <span class="token keyword">SELECT</span>
    last_name <span class="token punctuation">,</span>
    department_name <span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>location_id <span class="token punctuation">,</span>
    city
<span class="token keyword">FROM</span>
    employees <span class="token number">e</span><span class="token punctuation">,</span>
    departments <span class="token number">d</span><span class="token punctuation">,</span>
    locations l
<span class="token keyword">WHERE</span>
    <span class="token number">e</span><span class="token punctuation">.</span>department_id <span class="token operator">=</span> <span class="token number">d</span><span class="token punctuation">.</span>department_id
    <span class="token operator">AND</span> <span class="token number">d</span><span class="token punctuation">.</span>location_id <span class="token operator">=</span> l<span class="token punctuation">.</span>location_id
    <span class="token operator">AND</span> <span class="token number">e</span><span class="token punctuation">.</span>commission_pct <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name</span>
 <span class="token keyword">SELECT</span>
    <span class="token number">e</span><span class="token punctuation">.</span>last_name <span class="token punctuation">,</span>
    <span class="token number">e</span><span class="token punctuation">.</span>job_id <span class="token punctuation">,</span>
    <span class="token number">e</span><span class="token punctuation">.</span>department_id <span class="token punctuation">,</span>
    <span class="token number">d</span><span class="token punctuation">.</span>department_name
<span class="token keyword">FROM</span>
    employees <span class="token number">e</span><span class="token punctuation">,</span>
    departments <span class="token number">d</span><span class="token punctuation">,</span>
    locations l
<span class="token keyword">WHERE</span>
    <span class="token number">e</span><span class="token punctuation">.</span>department_id <span class="token operator">=</span> <span class="token number">d</span><span class="token punctuation">.</span>department_id
    <span class="token operator">AND</span> <span class="token number">d</span><span class="token punctuation">.</span>location_id <span class="token operator">=</span> l<span class="token punctuation">.</span>location_id
    <span class="token operator">AND</span> l<span class="token punctuation">.</span>city <span class="token operator">=</span> <span class="token string">'Toronto'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#5.查询每个工种、每个部门的部门名、工种名和最低工资</span>
 <span class="token keyword">SELECT</span>
    <span class="token number">d</span><span class="token punctuation">.</span>department_name <span class="token punctuation">,</span>
    j<span class="token punctuation">.</span>job_title <span class="token punctuation">,</span>
    <span class="token function">MIN</span><span class="token punctuation">(</span><span class="token number">e</span><span class="token punctuation">.</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>
    employees <span class="token number">e</span> <span class="token punctuation">,</span>
    departments <span class="token number">d</span> <span class="token punctuation">,</span>
    jobs j
<span class="token keyword">WHERE</span>
    <span class="token number">e</span><span class="token punctuation">.</span>department_id <span class="token operator">=</span> <span class="token number">d</span><span class="token punctuation">.</span>department_id
    <span class="token operator">AND</span> <span class="token number">e</span><span class="token punctuation">.</span>job_id <span class="token operator">=</span> j<span class="token punctuation">.</span>job_id
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span>
    <span class="token number">d</span><span class="token punctuation">.</span>department_name <span class="token punctuation">,</span>
    j<span class="token punctuation">.</span>job_title <span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">#6.查询每个国家下的部门个数大于2的国家编号</span>
 <span class="token keyword">SELECT</span>
    l<span class="token punctuation">.</span>country_id <span class="token punctuation">,</span>
    <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span>
    locations l <span class="token punctuation">,</span>
    departments <span class="token number">d</span>
<span class="token keyword">WHERE</span>
    <span class="token number">d</span><span class="token punctuation">.</span>location_id <span class="token operator">=</span> l<span class="token punctuation">.</span>location_id
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span>
    country_id
<span class="token keyword">HAVING</span>
    <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">></span> <span class="token number">2</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#7 、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式</span>
<span class="token keyword">SELECT</span>
    <span class="token number">e</span><span class="token punctuation">.</span>last_name employees<span class="token punctuation">,</span>
    <span class="token number">e</span><span class="token punctuation">.</span>employee_id "Emp<span class="token comment" spellcheck="true">#",</span>
    m<span class="token punctuation">.</span>last_name manager<span class="token punctuation">,</span>
    m<span class="token punctuation">.</span>employee_id "Mgr<span class="token comment" spellcheck="true">#"</span>
<span class="token keyword">FROM</span>
    employees <span class="token number">e</span><span class="token punctuation">,</span>
    employees m
<span class="token keyword">WHERE</span>
    <span class="token number">e</span><span class="token punctuation">.</span>manager_id <span class="token operator">=</span> m<span class="token punctuation">.</span>employee_id
    <span class="token operator">AND</span> <span class="token number">e</span><span class="token punctuation">.</span>last_name <span class="token operator">=</span> <span class="token string">'kochhar'</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">#二、sql99语法</span>
<span class="token comment" spellcheck="true">/*
语法：
    select 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名 
    on 连接条件
    【where 筛选条件】
    【group by 分组】
    【having 筛选条件】
    【order by 排序列表】


分类：
内连接（★）：inner
外连接
    左外(★):left 【outer】
    右外(★)：right 【outer】
    全外：full【outer】
交叉连接：cross 

*/</span>
<span class="token comment" spellcheck="true">#一）内连接</span>
<span class="token comment" spellcheck="true">/*
语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/</span>
<span class="token comment" spellcheck="true">#二、外连接</span>

 <span class="token comment" spellcheck="true">/*
 应用场景：用于查询一个表中有，另一个表没有的记录

 特点：
 1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
 */</span></code></pre>
<h3 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h3><pre class=" language-sql"><code class="language-sql"><span class="token comment" spellcheck="true">#进阶7：子查询</span>
<span class="token comment" spellcheck="true">/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
    select后面：
        仅仅支持标量子查询

    from后面：
        支持表子查询
    where或having后面：★
        标量子查询（单行） √
        列子查询  （多行） √

        行子查询

    exists后面（相关子查询）
        表子查询
按结果集的行列数不同：
    标量子查询（结果集只有一行一列）
    列子查询（结果集只有一列多行）
    行子查询（结果集有一行多列）
    表子查询（结果集一般为多行多列）
*/</span>
<span class="token comment" spellcheck="true">#一、where或having后面</span>
<span class="token comment" spellcheck="true">/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> &lt; >= &lt;= = &lt;>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

*/</span></code></pre>
<h3 id="分页查询"><a href="#分页查询" class="headerlink" title="分页查询"></a>分页查询</h3><pre class=" language-sql"><code class="language-sql"><span class="token comment" spellcheck="true">#进阶8：分页查询 ★</span>
<span class="token comment" spellcheck="true">/*

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
    select 查询列表
    from 表
    【join type join 表2
    on 连接条件
    where 筛选条件
    group by 分组字段
    having 分组后的筛选
    order by 排序的字段】
    limit 【offset,】size;

    offset要显示条目的起始索引（起始索引从0开始）
    size 要显示的条目个数
特点：
    ①limit语句放在查询语句的最后
    ②公式
    要显示的页数 page，每页的条目数size

    select 查询列表
    from 表
    limit (page-1)*size,size;

    size=10
    page  
    1    0
    2      10
    3    20
*/</span></code></pre>

      
    </div>
    <div class="article-footer">
      <blockquote class="mt-2x">
  <ul class="post-copyright list-unstyled">
    
    <li class="post-copyright-link hidden-xs">
      <strong>本文链接：</strong>
      <a href="http://dreamcat.ink/2019/07/14/chang-yong-de-sql-yu-ju/" title="常用的sql语句" target="_blank" rel="external">http://dreamcat.ink/2019/07/14/chang-yong-de-sql-yu-ju/</a>
    </li>
    
    <li class="post-copyright-license">
      <strong>版权声明： </strong> 本博客所有文章除特别声明外，均采用 <a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN协议</a> 许可协议。转载请注明出处！
    </li>
  </ul>
</blockquote>


<div class="panel panel-default panel-badger">
  <div class="panel-body">
    <figure class="media">
      <div class="media-left">
        <a href="https://github.com/DreamCats" target="_blank" class="img-burn thumb-sm visible-lg">
          <img src="/images/avatar.jpg" class="img-rounded w-full" alt="">
        </a>
      </div>
      <div class="media-body">
        <h3 class="media-heading"><a href="https://github.com/DreamCats" target="_blank"><span class="text-dark">Dreamcat</span><small class="ml-1x">ドリームキャット</small></a></h3>
        <div>个人简介。</div>
      </div>
    </figure>
  </div>
</div>


    </div>
  </article>
  
    
  <section id="comments">
  	
      <div id="vcomments"></div>
    
  </section>


  
</div>

  <nav class="bar bar-footer clearfix" data-stick-bottom>
  <div class="bar-inner">
  
  <ul class="pager pull-left">
    
    <li class="prev">
      <a href="/2019/07/17/stringbuffer-he-stringbuilder-yuan-li-jie-xi/" title="StringBuffer和StringBuilder原理解析"><i class="icon icon-angle-left" aria-hidden="true"></i><span>&nbsp;&nbsp;上一篇</span></a>
    </li>
    
    
    <li class="next">
      <a href="/2019/07/11/shou-ji-chang-yong-de-wang-zhan-chi-xu-geng-xin.../" title="收集常用的网站(持续更新...)"><span>下一篇&nbsp;&nbsp;</span><i class="icon icon-angle-right" aria-hidden="true"></i></a>
    </li>
    
    
    <li class="toggle-toc">
      <a class="toggle-btn collapsed" data-toggle="collapse" href="#collapseToc" aria-expanded="false" title="文章目录" role="button">
        <span>[&nbsp;</span><span>文章目录</span>
        <i class="text-collapsed icon icon-anchor"></i>
        <i class="text-in icon icon-close"></i>
        <span>]</span>
      </a>
    </li>
    
  </ul>
  
  
  <!-- Button trigger modal -->
  <button type="button" class="btn btn-fancy btn-donate pop-onhover bg-gradient-warning" data-toggle="modal" data-target="#donateModal"><span>赏</span></button>
  <!-- <div class="wave-icon wave-icon-danger btn-donate" data-toggle="modal" data-target="#donateModal">
    <div class="wave-circle"><span class="icon"><i class="icon icon-bill"></i></span></div>
  </div> -->
  
  
  <div class="bar-right">
    
    <div class="share-component" data-sites="weibo,qq,wechat,facebook,twitter" data-mobile-sites="weibo,qq,qzone"></div>
    
  </div>
  </div>
</nav>
  
<!-- Modal -->
<div class="modal modal-center modal-small modal-xs-full fade" id="donateModal" tabindex="-1" role="dialog">
  <div class="modal-dialog" role="document">
    <div class="modal-content donate">
      <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
      <div class="modal-body">
        <div class="donate-box">
          <div class="donate-head">
            <p>感谢您的支持，我会继续努力的!</p>
          </div>
          <div class="tab-content">
            <div role="tabpanel" class="tab-pane fade active in" id="alipay">
              <div class="donate-payimg">
                <img src="/images/donate/alipayimg.png" alt="扫码支持" title="扫一扫" />
              </div>
              <p class="text-muted mv">扫码打赏，你说多少就多少</p>
              <p class="text-grey">打开支付宝扫一扫，即可进行扫码打赏哦</p>
            </div>
            <div role="tabpanel" class="tab-pane fade" id="wechatpay">
              <div class="donate-payimg">
                <img src="/images/donate/wechatpayimg.png" alt="扫码支持" title="扫一扫" />
              </div>
              <p class="text-muted mv">扫码打赏，你说多少就多少</p>
              <p class="text-grey">打开微信扫一扫，即可进行扫码打赏哦</p>
            </div>
          </div>
          <div class="donate-footer">
            <ul class="nav nav-tabs nav-justified" role="tablist">
              <li role="presentation" class="active">
                <a href="#alipay" id="alipay-tab" role="tab" data-toggle="tab" aria-controls="alipay" aria-expanded="true"><i class="icon icon-alipay"></i> 支付宝</a>
              </li>
              <li role="presentation" class="">
                <a href="#wechatpay" role="tab" id="wechatpay-tab" data-toggle="tab" aria-controls="wechatpay" aria-expanded="false"><i class="icon icon-wepay"></i> 微信支付</a>
              </li>
            </ul>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>



</main>

  <footer class="footer" itemscope itemtype="http://schema.org/WPFooter">
	
	
    <ul class="social-links">
    	
        <li><a href="https://github.com/DreamCats" target="_blank" title="Github" data-toggle=tooltip data-placement=top><i class="icon icon-github"></i></a></li>
        
        <li><a href="https://github.com/DreamCats" target="_blank" title="Weibo" data-toggle=tooltip data-placement=top><i class="icon icon-weibo"></i></a></li>
        
        <li><a href="https://github.com/DreamCats" target="_blank" title="Twitter" data-toggle=tooltip data-placement=top><i class="icon icon-twitter"></i></a></li>
        
        <li><a href="https://github.com/DreamCats" target="_blank" title="Behance" data-toggle=tooltip data-placement=top><i class="icon icon-behance"></i></a></li>
        
        <li><a href="/atom.xml" target="_blank" title="Rss" data-toggle=tooltip data-placement=top><i class="icon icon-rss"></i></a></li>
        
    </ul>

    <div class="copyright">
    	
        <div class="publishby">
        	Theme by <a href="https://github.com/cofess" target="_blank"> cofess </a>base on <a href="https://github.com/cofess/hexo-theme-pure" target="_blank">pure</a>.
        </div>
    </div>
</footer>
  <script src="//cdn.jsdelivr.net/npm/jquery@1.12.4/dist/jquery.min.js"></script>
<script>
window.jQuery || document.write('<script src="js/jquery.min.js"><\/script>')
</script>

<script src="/js/plugin.min.js"></script>


<script src="/js/application.js"></script>


    <script>
(function (window) {
    var INSIGHT_CONFIG = {
        TRANSLATION: {
            POSTS: '文章',
            PAGES: '页面',
            CATEGORIES: '分类',
            TAGS: '标签',
            UNTITLED: '(未命名)',
        },
        ROOT_URL: '/',
        CONTENT_URL: '/content.json',
    };
    window.INSIGHT_CONFIG = INSIGHT_CONFIG;
})(window);
</script>

<script src="/js/insight.js"></script>






   




   
    
  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src="//cdn.jsdelivr.net/npm/valine"></script>
  <script type="text/javascript">
  var GUEST = ['nick', 'mail', 'link'];
  var meta = 'nick,mail,link';
  meta = meta.split(',').filter(function(item) {
    return GUEST.indexOf(item) > -1;
  });
  new Valine({
    el: '#vcomments',
    verify: false,
    notify: false,
    appId: '',
    appKey: '',
    placeholder: 'Just go go',
    avatar: 'mm',
    meta: meta,
    pageSize: '10' || 10,
    visitor: false
  });
  </script>

     







</body>
</html>